package core;

import java.io.BufferedInputStream;
import java.io.File;
import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;

import dataBase.DbConnection;

import xml.MyDom4j;

public class DataObtain {

	private static Element GetTableElement(Element root, String tableName) {
		@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = root.elementIterator("TABLE");
		while (tbIt.hasNext()) {
			Element table = tbIt.next();
			if (table.attributeValue("TABLE_NAME").equalsIgnoreCase(tableName)) {
				return table;
			}
		}
		System.out.println("GetTableElement failed");
		return null;
	}

	private static List<String> GetColNameList(Element structRoot,
			String dbType, String tableName) throws DocumentException,
			Exception {

		if (!structRoot.attributeValue("DBTYPE").equalsIgnoreCase(dbType)) {
			System.out.println("DATABASE MATCH FAILED");
			return null;
		}

		Element table = GetTableElement(structRoot, tableName);

		if (table == null) {
			System.out.println("TABLE NOT EXIST");
		} else {
			List<String> colNameList = new ArrayList<String>();
			colNameList.add(tableName);
			@SuppressWarnings("unchecked")
			Iterator<Element> fIt = table.elementIterator("FIELD");
			if (fIt.hasNext()) {
				while (fIt.hasNext()) {
					colNameList.add(fIt.next().elementText("COLUMN_NAME"));
				}
				// System.out.println(colNameList);
				return colNameList;
			} else {
				System.out.println("NO FIELD");
				return null;
			}
		}
		System.out.println("GetColNameList failed");
		return null;
	}

	private static String CreateSelectString(List<String> colNameList) {
		if (colNameList == null) {
			System.out.println("ColNameList IS NULL");
			return null;
		} else {

			int colnum = colNameList.size();
			if (colnum == 1) {
				System.out.println("No COLUMN");
				return null;
			}

			String selectString = "SELECT ";
			String tbName = colNameList.get(0);
			selectString += colNameList.get(1);

			if (colnum == 2) {
				;
			} else {
				for (int i = 2; i < colnum; i++) {
					selectString += "," + colNameList.get(i);
				}
			}
			selectString += "\n" + "FROM " + tbName + "\n";
			return selectString;
		}
	}

	private static List<Boolean> BinaryFilter(List<String> colTypeList,
			Element dataTypeElement) throws DocumentException, Exception {
		// System.out.println(dataTypeElement.asXML());
		List<Boolean> booleanList = new ArrayList<Boolean>();
		for (int i = 0; i < colTypeList.size(); i++) {
			String text = dataTypeElement.elementText(colTypeList.get(i));
			// System.out.println(text);
			if (text.equalsIgnoreCase("Base64Binary")) {
				booleanList.add(true);
			} else {
				booleanList.add(false);
			}
		}
		return booleanList;
	}

	private static List<String> GetColumnTypeList(String tbName,
			Element structRoot) {

		Element table = null;
		List<String> columnTypeList = new ArrayList<String>();
		@SuppressWarnings("unchecked")
		Iterator<Element> tIt = structRoot.elementIterator("TABLE");
		while (tIt.hasNext()) {
			Element element = tIt.next();
			if (element.attributeValue("TABLE_NAME").equalsIgnoreCase(tbName)) {
				table = element;
				break;
			}
		}
		@SuppressWarnings("unchecked")
		Iterator<Element> fIt = table.elementIterator("FIELD");
		while (fIt.hasNext()) {
			Element field = fIt.next();
			// System.out.println(field.asXML());
			columnTypeList.add(field.elementText("TYPE_NAME"));
		}
		// System.out.println(columnTypeList);
		return columnTypeList;
	}

	// private static List<String> GetBinaryColumnList(List<String> colNameList,
	// List<Boolean> binaryFilterList) {
	// List<String> binaryColumnList = new ArrayList<String>();
	// for (int i = 0; i < colNameList.size() - 1; i++) {
	// if (binaryFilterList.get(i)) {
	// binaryColumnList.add(colNameList.get(i + 1));
	// }
	// }
	// return binaryColumnList;
	// }

	// private static List<String> GetNonBinaryColumnList(
	// List<String> colNameList, List<Boolean> binaryFilterList) {
	// List<String> nonBinaryColumnList = new ArrayList<String>();
	// for (int i = 0; i < colNameList.size() - 1; i++) {
	// if (!binaryFilterList.get(i)) {
	// nonBinaryColumnList.add(colNameList.get(i + 1));
	// }
	// }
	// return nonBinaryColumnList;
	// }

	public static Document ObtainData(DbConnection dbCon, String structFile,
			List<String> tableList) throws DocumentException, SQLException,
			Exception {
		File file = new File(structFile);
		if (!file.exists()) {
			System.out
					.println("StructFile does't exist ObtainData can't begin");
			return null;
		}

		Document structDoc = MyDom4j.Read(structFile);
		String dbType = structDoc.getRootElement().attributeValue("DBTYPE");
		Element structRoot = structDoc.getRootElement();

		Document dataTypdDoc = MyDom4j
				.Read("C:\\Users\\Administrator\\workspace\\Graduate\\xml\\DataType.xml");
		Element dataTypeElement = dataTypdDoc.getRootElement().element(dbType);

		Document doc = MyDom4j.CreatDocumentRoot("DATA");
		Element root = doc.getRootElement();

		root.addAttribute("STRUCT", structFile);

		try {
			for (int tbindex = 0; tbindex < tableList.size(); tbindex++) {
				String tbName = tableList.get(tbindex);
				List<String> colNameList = GetColNameList(structRoot,
						dbCon.GetCurrentDbType(), tbName);

				List<String> colTypeList = GetColumnTypeList(tbName, structRoot);

				List<Boolean> binaryFilterList = BinaryFilter(colTypeList,
						dataTypeElement);
				Element table = root.addElement("TABLE");
				table.addAttribute("TABLE_NAME", colNameList.get(0));

				String sql = CreateSelectString(colNameList);
				// System.out.println(sql);
				ResultSet rs = dbCon.ExcuteStatement(sql);

				while (rs.next()) {
					Element field = table.addElement("FIELD");
					for (int colindex = 1; colindex < colNameList.size(); colindex++) {
						Element cell = field.addElement(colNameList
								.get(colindex));
						if (!binaryFilterList.get(colindex - 1)) {
							String text = rs.getString(colindex);
							if (text != null) {
								text = text.trim();
								cell.addText(text);
							}
						} else {
							Blob blob = rs.getBlob(colindex);
							BufferedInputStream bis = new BufferedInputStream(
									blob.getBinaryStream());
							byte[] buffer = new byte[3072];
							char[] charBuffer;
							StringBuffer strBuffer = new StringBuffer();
							int count = -1;
							while ((count = bis.read(buffer, 0, 3072)) != -1) {
								charBuffer = Base64.EncodeBase64toChar(buffer,
										count);
								strBuffer.append(charBuffer);
							}
							cell.addText(strBuffer.toString());
						}
					}
				}
				rs.close();
			}

		} catch (Exception ex) {
			throw ex;
		}
		return doc;
	}

	public static void main(String[] args) throws DocumentException,
			SQLException, Exception {

	}
}

// private static String GetPrimekeyType(String pk, String tbName,
// Element structRoot, Element dataTypeElement) {
// Element table = null;
// @SuppressWarnings("unchecked")
// Iterator<Element> tIt = structRoot.elementIterator("TABLE");
// while (tIt.hasNext()) {
// Element element = tIt.next();
// if (element.attributeValue("TABLE_NAME").equalsIgnoreCase(tbName)) {
// table = element;
// break;
// }
// }
//
// String type = null;
// @SuppressWarnings("unchecked")
// Iterator<Element> fIt = table.elementIterator("FIELD");
// while (fIt.hasNext()) {
// Element field = fIt.next();
// if (field.elementText("COLUMN_NAME").equalsIgnoreCase(pk)) {
// // System.out.println(field.elementText("TYPE_NAME"));
// type = field.elementText("TYPE_NAME");
// break;
// }
// }
//
// type = dataTypeElement.elementText(type);
// // System.out.println(type);
// return type;
// }

// if (binaryColumnList.size() > 0) {
//
// List<String> pkList = new ArrayList<String>();
// if (binaryColumnList.size() > 0) {
// pkList = DbInfo.GetPrimeKey(dbCon.GetConnection(),
// tbName);
// if (pkList != null) {
// ;
// } else {
// if (nonBinaryColumnList.size() >= 1) {
// pkList.add(nonBinaryColumnList.get(0));
// } else {
// pkList = null;
// }
// }
// }
//
// List<String> pkTypeList = new ArrayList<String>();
// for (int i = 0; i < pkList.size(); i++) {
// pkTypeList.add(GetPrimekeyType(pkList.get(i), tbName,
// structRoot, dataTypeElement));
// }
//
// String getPkSql = "SELECT " + pkList.get(0);
// for (int i = 1; i < pkList.size(); i++) {
// getPkSql += "," + pkList.get(i);
// }
// getPkSql += " FROM " + tbName;
// rs = dbCon.ExcuteStatement(getPkSql);
//
// String preSql = "SELECT "+binaryColumnList.get(0);
// for(int i=1;i<binaryColumnList.size();i++)
// {
// preSql+=","+binaryColumnList.get(i);
// }
// preSql+=" FROM "+tbName;
// // System.out.println(preSql);
//
// while (rs.next()) {
//
//
// }

// PreparedStatement ps = dbCon
// .GetPreparedStatement(preSql);
// while (rs.next()) {
//
// for (int t = 0; t < pkList.size(); t++) {
// if (pkTypeList.get(t).equalsIgnoreCase("int")) {
// ps.setInt(t + 1, rs.getInt(pkList.get(t)));
// }
// if (pkTypeList.get(t)
// .equalsIgnoreCase("String")) {
// ps.setString(t + 1,
// rs.getString(pkList.get(t)));
// }
// }

// for (int i = 0; i < binaryColumnList.size(); i++) {
//
// String binaryColumn = binaryColumnList.get(i);
// String preSql = "SELECT " + binaryColumnList.get(i)
// + " FROM " + tbName + " WHERE ";
// preSql += pkList.get(0) + "=?";
// for (int k = 1; k < pkList.size(); k++) {
// preSql += " AND " + pkList.get(k) + "=?";
// }
//

//
// System.out.println(preSql);
//
// ResultSet blobRs = ps.executeQuery();
// String binaryStream = null;
// while (blobRs.next()) {
// Blob blob = blobRs.getBlob(1);
// BufferedInputStream bis = new BufferedInputStream(
// blob.getBinaryStream());
// byte[] buffer = new byte[3072];
// char[] charBuffer;
//
// StringBuffer strBuffer = new StringBuffer();
//
// int count = -1;
// while ((count = bis.read(buffer, 0, 3072)) != -1) {
// charBuffer = Base64.EncodeBase64toChar(buffer,
// count);
// strBuffer.append(charBuffer);
// }
// // System.out.println(strBuffer);
// binaryStream = strBuffer.toString();
// }
// @SuppressWarnings("unchecked")
// Iterator<Element> fIt = table.elementIterator("FIELD");
// while (fIt.hasNext()) {
// fIt.next().element(binaryColumn)
// .setText(binaryStream);
// }
// }
// }